<?php
App::uses('AppController', 'Controller');
App::uses('Sanitize', 'Utility');
/**
 * Tables Controller
 *
 * @property Table $Table
 */
class DynamiquesController extends AppController {

	var $components = array('Maconnexion', 'Session','Logging');
	public $helpers = array('Date', 'Datefr', 'Moninput', 'Session_gpp');
/**
 * RLEB
 *
 *
 * @return void
 */
	public function DynamiqueOfTable($table_id, $version_id) {

		$table_id = Sanitize::paranoid($table_id);
		$table_id = Sanitize::clean($table_id, array('encode' => false, 'unicode'=> true));

		$version_id = Sanitize::paranoid($version_id);
		$version_id = Sanitize::clean($version_id, array('encode' => false, 'unicode'=> true));
		
		$versionencours = $this->Dynamique->Table->Version->find('first',array('conditions' => array('Version.id' => $version_id),'recursive'=>-1));
		
		$satable=$this->Dynamique->Table->find('first',array('conditions' => array('Table.id' => $table_id),'recursive'=>-1));
		$saversion=$this->Dynamique->Table->Version->find('first',array('conditions' => array('Version.id' => $satable['Table']['version_id']),'recursive'=>-1));
    	$sonapp = $this->Dynamique->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));

		$colonnes = $this->Dynamique->Table->Colonne->find('all', array( 'conditions' => array('Colonne.table_id =' => $table_id),'recursive'=>-1 ));
		$sapk = $this->Dynamique->Table->Colonne->find('all', array('conditions' => array('Colonne.table_id =' => $table_id, 'Colonne.topPk =' => '1'),'recursive'=>-1 ));

		$this->set('sonapp', $sonapp);
		$this->set('saversion', $saversion);
		$this->set('satable', $satable);
		if (isset($colonnes)){
			$this->set('colonnes', $colonnes);
		}

		$this->set('versionencours', $versionencours);

		$this->render("index");
	}

	private function purge_fichier($dossier_traite = null ){
		
		$age_requis=3600;

		$dossier_traite = Sanitize::paranoid($dossier_traite,array('_', DS));
		//$extension_choisie = Sanitize::paranoid($extension_choisie);
		$age_requis = Sanitize::paranoid($age_requis);

		if($dossier_traite!= null){
			$deb_chemin=APP.'out_fichiers'.DS.$dossier_traite;
		}else{
			$deb_chemin=APP.'out_fichiers';
		}
		 
		$repertoire = opendir($deb_chemin);

		// On lance notre boucle qui lira les fichiers un par un.
	    while(false !== ($fichier = readdir($repertoire)))
	    {
	    // On met le chemin du fichier dans une variable simple
	    	$chemin = $deb_chemin.DS.$fichier;
	            
		    // Les variables qui contiennent toutes les infos nécessaires.
		    $infos = pathinfo($chemin);
		    //$extension = $infos['extension'];

		    $age_fichier = time() - filemtime($chemin);

		// On n'oublie pas LA condition sous peine d'avoir quelques surprises. :p
		    if($fichier!=".svn" AND $fichier!="." AND $fichier!=".." /*AND !is_dir($fichier)*/ /*AND $extension == $extension_choisie*/ AND $age_fichier > $age_requis)
	        {
	        	if ( !is_dir($fichier) && isset($infos['extension']) /*AND $extension == $extension_choisie*/ AND $age_fichier > $age_requis){
			        unlink($chemin);
		        }elseif ($age_fichier > $age_requis) {
		        	if($dossier_traite!= null){
						$this->purge_fichier( $dossier_traite.DS.$fichier);
					}else{
						$this->purge_fichier($fichier);
					}
		        	rmdir($chemin);
		        }
	        }
	    }
		closedir($repertoire); // On ferme !
	}

	public function EnvoyerFichier($path, $filename) {
		$this->viewClass = 'Media'; 
		$log = 0;

		$path = Sanitize::paranoid($path);
		$filename = Sanitize::paranoid($filename,array('.', '_','$','[',']'));

		$extension = substr($filename, -3);
		$pos = strpos($filename, '.');
		$name = substr($filename, 0, $pos);
		//$this['passedArgs']=var_dump(json_decode($this['passedArgs'], true));

		if($log>0){
			$this->Logging->lfile(APP.'log'.DS.'GenererFichier.log');
			$this->Logging->lwrite('------------------------------------------------------------------------------');
			$this->Logging->lwrite('EnvoyerFichier');
			$this->Logging->lwrite($filename);
			$this->Logging->lwrite($name);
			$this->Logging->lwrite($path);
			if($log>1){
				foreach($this as $namepara => $valparam){
					$this->Logging->lwrite($namepara.' :'.$valparam);
				}
			}
		}

		  $params = array( 
	           'id' => $filename ,
	           'name' => $name , 
	           'download' => true, 
	           'extension' => $extension, 
	           'path' => APP . 'out_fichiers' . DS . $path. DS
	     ); 
	    
	    if($log>0){
			$this->Logging->lwrite('Fin EnvoyerFichier');
			$this->Logging->lwrite('------------------------------------------------------------------------------');
			$this->Logging->lclose();
		}

   		$this->set($params);
	}

	public function ProgressBar_ajax($idunique) {
		$log = 1;

		if($log>0){
			$this->Logging->lfile(APP.'log'.DS.'GenererFichier.log');
			$this->Logging->lwrite('------------------------------------------------------------------------------');
			$this->Logging->lwrite('Debut ProgressBar_ajax');
		}

		$this->layout = 'ajax';

		$idunique = Sanitize::paranoid($idunique);

		$filename=APP.'out_fichiers'. DS .$idunique. DS .'pb.txt';
		$pb = fopen($filename ,'r') or exit (__("No create file!"));
		$contents = fread($pb, filesize($filename));
		fclose($pb);


		if(is_numeric($contents)){
			echo $contents;
		}else{
			echo 0;
		}

		if($log>0){
			$this->Logging->lwrite('Fin ProgressBar_ajax');
			$this->Logging->lwrite('------------------------------------------------------------------------------');
			$this->Logging->lclose();
		}

		exit();
	}

	public function PopupGenererScript($table_id, $rupture_id=0) {
		
		$this->layout = 'popup';

		$table_id = Sanitize::paranoid($table_id);

		$satable=$this->Dynamique->Table->find('first',array('conditions' => array('Table.id' => $table_id),'recursive'=>-1));
		$idunique=uniqid (/*$satable['Table']['name']*/);

		$this->set('idunique', $idunique);
		$this->set('rupture_id', $rupture_id);
		$this->set('satable', $satable);
		$this->render("PopupGenererScript");
	}

	public function GenererScript_ajax($table_id=0, $idunique=0, $rupture_id=0) {
		
		$this->layout = 'ajax';
		$log = 1;

		if($log>0){
			$this->Logging->lfile(APP.'log'.DS.'GenererFichier.log');
			$this->Logging->lwrite('------------------------------------------------------------------------------');
			$this->Logging->lwrite('Debut GenererFichier');
		}

		if (isset($this->request->data['table_id'])){
			$table_id=$this->request->data['table_id'];
			if($log>0){$this->Logging->lwrite('table_id :'.$table_id);}
		}
		if (isset($this->request->data['idunique'])){
			$idunique=$this->request->data['idunique'];
			if($log>0){$this->Logging->lwrite('idunique :'.$idunique);}
		}
		if (isset($this->request->data['rupture_id'])){
			$rupture_id=$this->request->data['rupture_id'];
			if($log>0){$this->Logging->lwrite('rupture_id :'.$rupture_id);}
		}

		//suppression des caracteres non alphanumerique
		$table_id = Sanitize::paranoid($table_id);
		$idunique = Sanitize::paranoid($idunique);

		$desc_fic['path']=APP.'out_fichiers'.DS .$idunique;

		//creation d'un fichier pb pour logguer l'avancement pour la progressbar
		mkdir($desc_fic['path'], 0777);
		$pb = fopen($desc_fic['path']. DS .'pb.txt' ,'w') or exit (__("No create file!"));
		fwrite($pb, '0');
		fclose($pb);
		
		if($log>0){ $this->Logging->lwrite('before purge_fichier'); }
		//purge des fichier de + d'1 heure
		$this->purge_fichier();
		if($log>0){ $this->Logging->lwrite('after purge_fichier'); }
		
		//recuperaion classique des données liée a la table a traitée
		$satable=$this->Dynamique->Table->find('first',array('conditions' => array('Table.id' => $table_id),'recursive'=>-1));
		$saversion=$this->Dynamique->Table->Version->find('first',array('conditions' => array('Version.id' => $satable['Table']['version_id']),'recursive'=>-1));
    	$sonapp = $this->Dynamique->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));
		$sescolonnes = $this->Dynamique->Table->Colonne->find('all', array( 'conditions' => array('Colonne.table_id =' => $table_id),'recursive'=>-1 ));
		$sapk = $this->Dynamique->Table->Colonne->find('all', array('conditions' => array('Colonne.table_id =' => $table_id, 'Colonne.topPk =' => '1'),'recursive'=>-1 ));

		$whereclause='';
		if(isset($rupture_id) and $rupture_id!=0){
			$larupture=$this->Dynamique->Table->Rupture->find('first', array( 'conditions' => array('Rupture.id =' => $rupture_id),'recursive'=>-1 ));
			//$larupture=$larupture['Rupture']['whereclause'];
			if($larupture['Rupture']['whereclause']!='NULL' and trim($larupture['Rupture']['whereclause'])!=''){
				$whereclause=' where '.$larupture['Rupture']['whereclause'];
				$whereclause=str_replace("\'","'",$whereclause);
				$whereclause=str_replace("\\n"," ",$whereclause);
				$whereclause=str_replace("\n"," ",$whereclause);
				//$whereclause=' where '.$larupture;
			}
		}

		$desc_fic['filename']=$satable['Table']['name'].'.sql';
		$desc_fic['name']=$satable['Table']['name'];
		$desc_fic['extension']='sql';

		//création de la liste des colonnes pour le select d'extraction des données
		$lst_colonnes='';
		foreach ($sescolonnes as $colonne) {
			$colonne=$colonne['Colonne'];
			if($lst_colonnes==''){
				$lst_colonnes=$colonne['name'];
			}else{
				$lst_colonnes=$lst_colonnes.', '.$colonne['name'];
			}
		}

		//création de la liste des colonnes Primary Key pour le select d'extraction des données
		$lst_pk='';
		foreach ($sapk as $pk) {
			if($lst_pk==''){
				$lst_pk=$pk['Colonne']['name'];
			}else{
				$lst_pk=$lst_pk.', '.$pk['Colonne']['name'];
			}
		}

		
		//on compte le nb de données a extraire
		$requete='SELECT count(*) as NB ';
		if ($sonapp['Application']['hebergement']==0){
			$requete = $requete." FROM ".$satable['Table']['name'].'_$'.$satable['Table']['id'].$whereclause;
		}else{
			$requete = $requete." FROM ".$satable['Table']['name'].$whereclause;
		}
		if($log>0){ $this->Logging->lwrite('nbTotalLignes requete: '.$requete); }
		$reponse = $this->Maconnexion->dmlquery($sonapp, $requete);
		$nbTotalLignes = $reponse[0]['NB'];
		if($log>0){ $this->Logging->lwrite('nbTotalLignes: '.$nbTotalLignes); }

		//préparation du select d'extraction des données
		$requete_select = "SELECT ".$lst_colonnes;
		if ($sonapp['Application']['hebergement']==0){
			$requete_select = $requete_select." FROM ".$satable['Table']['name'].'_$'.$satable['Table']['id'].$whereclause;
		}else{
			$requete_select = $requete_select." FROM ".$satable['Table']['name'].$whereclause;
		}
		if ($lst_pk!=''){
			$requete_select= $requete_select . ' Order by '.$lst_pk;
		}else{
			$requete_select= $requete_select . ' Order by Gestparamphp_id';
		}

		//on défini ici que l'on va traité les données par paquets de 1000 lignes
		$nb_enr=1000;

		$limit_min=-$nb_enr;
		$nbLignerestantes=$nbTotalLignes;
		//$nbLignerestantes=99;

		//définition du caractère de retour ligne (ici UNIX) + ouverture du fichier dans lequel on va ecrire les données
		$filename = Sanitize::paranoid($desc_fic['filename'],array('.', '_','$','[',']'));
		$nl = "\n";
		$fp = fopen($desc_fic['path'].DS .$filename ,'a') or exit (__("No create file!"));
		
		if(isset($larupture['Rupture']['entetescript']) and $larupture['Rupture']['entetescript']!='NULL' and trim($larupture['Rupture']['entetescript'])!=''){
			fwrite($fp, $larupture['Rupture']['entetescript'].$nl);
		}elseif($satable['Table']['entetescript']!='NULL' and trim($satable['Table']['entetescript'])!=''){
			fwrite($fp, $satable['Table']['entetescript'].$nl);
		}
		

		//on calcul le timeout du serveur en fonction du nombre de ligne a traité
		//on estime que 4 seconde es suffisant pour un pacquet de 1000 lignes
		set_time_limit(floor(4 * $nbTotalLignes / 1000));

		if($log>0){ $this->Logging->lwrite('nbLignerestantes: '.$nbLignerestantes); }
		while ($nbLignerestantes>0){
			$avancemment=floor(($nbTotalLignes-$nbLignerestantes)/$nbTotalLignes*100);
			
			$pb = fopen($desc_fic['path']. DS .'pb.txt' ,'w') or exit (__("No create file!"));
			fwrite($pb, $avancemment);
			fclose($pb);

			if($log>0){ $this->Logging->lwrite('Boucle while'); }
			$nbLignerestantes=$nbLignerestantes-$nb_enr;
			$limit_min=$limit_min+$nb_enr;

			//on extrait les données
			$requete=null;
			if ($sonapp['Application']['hebergement']==0){
				$requete = $requete_select.' limit '.($limit_min).','.$nb_enr;
			}else{
				//Mysql Distant
				if ($sonapp['Application']['pgdatabase_id']==1){
					$requete = $requete_select.' limit '.($limit_min).','.$nb_enr;
				}
				//Oracle distant
				elseif ($sonapp['Application']['pgdatabase_id']==2){
					$requete= "SELECT * FROM (
									SELECT ROWNUM rnum, t.*
	  								FROM (".$requete_select.')t )
	  									where  rnum between '.($limit_min+1).' AND '.($limit_min+$nb_enr);
					
				}
			}
			if($log>0){ $this->Logging->lwrite($requete); }

			//$requete = $requete_select.' limit '.($limit_min).','.$nb_enr;
			$reponse = null;
			$reponse =& $this->Maconnexion->p_dmlquery($sonapp, $requete );
			
			//pour chauqe ligne extraites
			while($task = $reponse->fetch()) {
				
				//on commence par generer un delete
				$req_delete='Delete from '.$satable['Table']['name'].' where' ;
				$req_delete_where='';
				if (count($sapk)!=0){
					foreach ($sapk as $pk) {
						if ($task[$pk['Colonne']['name']]==''){
							$req_delete_where=$req_delete_where.' and '.$pk['Colonne']['name']." is null";
						}elseif($pk['Colonne']['pgtypecol_id']!='1'){
							$req_delete_where=$req_delete_where.' and '.$pk['Colonne']['name']."=".$task[$pk['Colonne']['name']];
						}else{
							$req_delete_where=$req_delete_where.' and '.$pk['Colonne']['name']."='".$task[$pk['Colonne']['name']]."'";
						}
					}
				}else{
					foreach ($sescolonnes as $colonne) {
						if ($task[$colonne['Colonne']['name']]==''){
							$req_delete=$req_delete.' and '.$colonne['Colonne']['name']." is null";
						}elseif($colonne['Colonne']['pgtypecol_id']!='1'){
							$req_delete=$req_delete.' and '.$colonne['Colonne']['name']."=".$task[$colonne['Colonne']['name']];
						}else{
							$req_delete=$req_delete.' and '.$colonne['Colonne']['name']."='".$task[$colonne['Colonne']['name']]."'";
						}
					}
				}
				$req_delete_where=substr($req_delete_where, 4) .';';

			  	fwrite($fp, $req_delete.$req_delete_where.$nl);

			  	//ici on genere l'insert
			  	$req_insert_part1='' ;
			  	$req_insert_part2='(' ;
				foreach ($sescolonnes as $colonne) {
					if($req_insert_part1==''){
						$req_insert_part1=$colonne['Colonne']['name'];
					}else{
						$req_insert_part1=$req_insert_part1.', '.$colonne['Colonne']['name'];
					}
					
					if($colonne['Colonne']['pgtypecol_id']!='1'){
						$req_insert_part2=$req_insert_part2.", ".$task[$colonne['Colonne']['name']];
					}else{
						$task[$colonne['Colonne']['name']]=str_replace("'","''",$task[$colonne['Colonne']['name']]);
						$req_insert_part2=$req_insert_part2.", '".$task[$colonne['Colonne']['name']]."'";
					}
				}
				$req_insert_part2=str_replace("(,","(",$req_insert_part2);
				$req_insert_part2=str_replace("''","null",$req_insert_part2);
				$req_insert_part2=str_replace(", ,",", null,",$req_insert_part2);
				$req_insert_part2=str_replace("( ,","( null,",$req_insert_part2);
				$req_insert_part2=str_replace(", )",", null)",$req_insert_part2);
				$req_insert_part1='Insert into '.$satable['Table']['name'].' ('.$req_insert_part1.') values '.$req_insert_part2.');';
			  	fwrite($fp, $req_insert_part1.$nl);
			}
			$reponse->closeCursor($reponse);
		}
		//fwrite($fp, "commit;".$nl);
		if(isset($larupture['Rupture']) and $larupture['Rupture']['piedscript']!='NULL' and trim($larupture['Rupture']['piedscript'])!=''){
			fwrite($fp, $larupture['Rupture']['piedscript'].$nl);
		}elseif($satable['Table']['piedscript']!='NULL' and trim($satable['Table']['piedscript'])!=''){
			fwrite($fp, $satable['Table']['piedscript'].$nl);
		}
		fclose($fp);


		if($log>0){ $this->Logging->lwrite('Fin while'); }
		
		//on ecrit 100 dans le fichier de la progressbar
		$pb = fopen($desc_fic['path']. DS .'pb.txt' ,'w') or exit (__("No create file!"));
		fwrite($pb, "100");
		fclose($pb);
	   

	    if($log>0){
			$this->Logging->lwrite('Fin GenererFichier');
			$this->Logging->lwrite('------------------------------------------------------------------------------');
			$this->Logging->lclose();
		}

		//dans path, on met que le nom de répertoire pour pas visualiser le chemin complet dans les url
		$desc_fic['path']=$idunique;

		echo json_encode($desc_fic);
		exit;
	}

	public function ChargerLignes_ajax($table_id=null, $rupture_id=0){	
		$log = 1;
		$this->layout = 'ajax';


		$table_id = Sanitize::paranoid($table_id);
		$rupture_id = Sanitize::paranoid($rupture_id);
		$this->params['url'] = Sanitize::clean($this->params['url'], array('encode' => false, 'unicode'=> true));
		

		if($log>0){
			$this->Logging->lfile(APP.'log'.DS.'Dynamique_ChargerLignes_ajax.log');
			$this->Logging->lwrite('------------------------------------------------------------------------------');
			$this->Logging->lwrite('Debut ChargerLignes_ajax');

			if($log>1){
				foreach($this->params['url'] as $namepara => $valparam){
					$this->Logging->lwrite($namepara.' :'.$valparam);
				}
			}
		}

		$satable=$this->Dynamique->Table->find('first',array('conditions' => array('Table.id' => $table_id),'recursive'=>-1));
		$saversion=$this->Dynamique->Table->Version->find('first',array('conditions' => array('Version.id' => $satable['Table']['version_id']),'recursive'=>-1));
    	$sonapp = $this->Dynamique->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));

    	if($rupture_id!=0){
    		$larupture=$this->Dynamique->Table->Rupture->find('first',array('conditions' => array('Rupture.id' => $rupture_id),'recursive'=>-1));
    	}

		$sescolonnes = $this->Dynamique->Table->Colonne->find('all', array('conditions' => array('Colonne.table_id =' => $table_id), 'order'=>array('topPk desc, id asc'), 'recursive'=>-1 ));
		
		/* Construction de la liste des colonnes pour la requete sur la table
			et conctruction d'un tableau utilé pour la detection  du tri*/
		$i = 0;
		// si hebergement des lignes dans la bdd local
		if ($sonapp['Application']['hebergement']==0){
			$colonne_tableaux[0]['name']='RNUM';
			$colonne_tableaux[1]['name']='Gestparamphp_id';
			$lst_colonnes = '@rownum:=@rownum+1 AS RNUM';
			$lst_colonnes = $lst_colonnes.', Gestparamphp_id as ID';
			$i = 2;
		}else{
			// si hebergement des lignes dans la bdd distante Mysql
			if ($sonapp['Application']['pgdatabase_id']==1){
				$colonne_tableaux[0]['name'] = 'RNUM';
				$colonne_tableaux[1]['name'] = 'ID';
				$lst_colonnes = '@rownum:=@rownum+1 AS RNUM';
				$lst_colonnes = $lst_colonnes.', @rownum AS ID';
				$i = 2;
			}
			// si hebergement des lignes dans la bdd distante Oracle
			elseif ($sonapp['Application']['pgdatabase_id']==2){
				$colonne_tableaux[0]['name'] = 'ROWNUM';
				$colonne_tableaux[1]['name'] = 'ID';
				//$lst_colonnes = 'rownum AS RNUM';
				$lst_colonnes = 'rownum AS ID';
				$i = 2;
			}
		}

		foreach ($sescolonnes as $colonne) {
			$colonne=$colonne['Colonne'];
			$lst_colonnes=$lst_colonnes.', '.$colonne['name'];
			$colonne_tableaux[$i] = $colonne;
			$i = $i + 1;
		}

		//debug('SELECT count(*) FROM '.$satable['Table']['name'].'_$'.$satable['Table']['id']);
		//debug($reponse );
		/*if (isset($this->params['url']['iDisplayLength'])){
			$page = ceil(($this->params['url']['iDisplayStart']) / ($this->params['url']['iDisplayLength'])) + 1;
		}else{
			$page =1;
		}
		$this->Logging->lwrite('$page : '.$page);*/

		/* Gestion du tri*/
		$sort = array();
		if (isset($this->params['url']['iSortingCols']) ){
			for ($i = 0; $i < $this->params['url']['iSortingCols']; $i++) {
				$numero_colonne = $this->params['url']['iSortCol_'.$i];
				if($numero_colonne > 0){
					if($log>1){ $this->Logging->lwrite('$numero_colonne : '.$numero_colonne); }
					$sort[] = $colonne_tableaux[$numero_colonne]['name']. ' ' . $this->params['url']['sSortDir_'.$i];
					
					if($log>1){ $this->Logging->lwrite('$sort : '.$sort[0]); }
				}
				/*switch ($this->params['url']['mDataProp_'.$numero_colonne]) {
					case "User.NomPrenom":
						$sort[] = 'User.nom' . ' ' . $this->params['url']['sSortDir_'.$i];
						break;
					default:
						$sort[] = $this->params['url']['mDataProp_'.$numero_colonne] . ' ' . $this->params['url']['sSortDir_'.$i];
				}*/
			}
		}else{
			$sort[] = 'ID asc';
		}

		/* Gestion du filtre*/
		if (isset($this->params['url']['iColumns']) ){
			$conditions = array();
			for ($i = 0; $i < $this->params['url']['iColumns']; $i++) {
				if(!empty($this->params['url']['sSearch_'.$i])) {
					//$conditions[] = $colonne_tableaux[$i+1]['name']." like '%".$this->params['url']['sSearch_'.$i]."%'"; 
					if (!ereg('[%]',$this->params['url']['sSearch_'.$i])){
						$conditions[] = $colonne_tableaux[$i+1]['name']." = '".$this->params['url']['sSearch_'.$i]."'"; 
					}else{
						$conditions[] = $colonne_tableaux[$i+1]['name']." like '".$this->params['url']['sSearch_'.$i]."'"; 
					}
					/*switch ($this->params['url']['mDataProp_'.$i]) {
						case "BusinessUnit.name":
							$conditions[] = array('Task.business_unit_id' => $this->params['url']['sSearch_'.$i]); 
							break;
						case "Task.libelle":
							$conditions[] = array('Task.libelle LIKE' => '%'.$this->params['url']['sSearch_'.$i].'%'); 
							break;
						case "TypeTask.libelle":
							$conditions[] = array('Task.type_task_id' => $this->params['url']['sSearch_'.$i]); 
							break;
						case "EtatTask.libelle":
							$conditions[] = array('Task.etat_task_id' => $this->params['url']['sSearch_'.$i]); 
							break;
						case "User.NomPrenom":
							$conditions[] = array('Task.user_id' => $this->params['url']['sSearch_'.$i]); 
							break;
						default:
							$conditions[] = array($this->params['url']['mDataProp_'.$i] => $this->params['url']['sSearch_'.$i]); 
					}*/
				}
			}
		}
		$where_clause = "1=1 ";
		if(!empty($conditions[0])) {
			foreach ($conditions as $condition) {
				$where_clause=$where_clause.' and '.$condition;
			}
		}
		if($rupture_id!=0){
    		$where_clause=$where_clause.' and (1=1 and '.str_replace("\'","'",$larupture['Rupture']['whereclause'].')');
    	}
		if($log > 0){ $this->Logging->lwrite('$where_clause : '.$where_clause); }

		/*$this->Task->recursive = 0;
		$tasks = $this->Task->find('all',
			array(
				'limit' => $this->params['url']['iDisplayLength'], 
				'page' => $page,
				'order' => $sort,
				'conditions' => $conditions
			)
		);*/

		/* Gestion du LIMIT*/
		if (isset($this->params['url']['iDisplayLength']) && is_numeric($this->params['url']['iDisplayLength'])){
			$debut_limite = floor($this->params['url']['iDisplayStart']) ;
			$nb_enregistrement= floor($this->params['url']['iDisplayLength']) ;
			if($log>0){
				$this->Logging->lwrite('iDisplayStart : '.$this->params['url']['iDisplayStart']);
				$this->Logging->lwrite('iDisplayLength : '.$this->params['url']['iDisplayLength']);
			}

		}else{
			$debut_limite= 0 ;
			$nb_enregistrement= 10;
		}
		$limit=	$debut_limite.",".$nb_enregistrement;
		if($log>0){ $this->Logging->lwrite('$limit : '.$limit); }


		/* Préparation de la requete SELECT*/
		$requete = "SELECT ".$lst_colonnes;
		if ($sonapp['Application']['hebergement']==0){
			$requete = $requete." FROM ".$satable['Table']['name'].'_$'.$satable['Table']['id'];
			$requete = $requete.',(SELECT @rownum:=0) r';
		}else{
			if ($sonapp['Application']['pgdatabase_id']==1){
				$requete = $requete." FROM ".$satable['Table']['name'];
				$requete = $requete.',(SELECT @rownum:=0) r';
			}elseif ($sonapp['Application']['pgdatabase_id']==2){
				$requete = $requete." FROM ".$satable['Table']['name'];
			}
		}

		//Mysql local
		if ($sonapp['Application']['hebergement']==0){
			$requete = $requete." where ".$where_clause;
			if (isset($sort[0])){
				$requete=$requete.' Order by '.$sort[0];
			}
			$requete=$requete.' LIMIT '.$limit;
		}else{
			//Mysql Distant
			if ($sonapp['Application']['pgdatabase_id']==1){
				$requete = $requete." where ".$where_clause;
				if (isset($sort[0])){
					$requete=$requete.' Order by '.$sort[0];
				}
				$requete=$requete.' LIMIT '.$limit;
			}
			//Oracle distant
			elseif ($sonapp['Application']['pgdatabase_id']==2){
					$requete = 'SELECT * FROM ('.$requete.") where ".$where_clause;
				if (isset($sort[0])){
					$requete=$requete.' Order by '.$sort[0];
				}
				$requete= "SELECT * FROM (
								SELECT ROWNUM rnum, t.*
  								FROM (".$requete.')t )
  									where  rnum between '.($debut_limite+1).' AND '.($debut_limite+$nb_enregistrement);
				
			}
		}
		
		//debug($requete );
		if($log > 0){ $this->Logging->lwrite('$requete : '.$requete); }
		$tasks = $this->Maconnexion->dmlquery($sonapp, $requete );

		/* Transfromation des résultat de la requete pour envoyer a datatables*/
		//$i=0;
		//$tasks=null;
		//if ($reponse!=null){
		//	$tasks=$reponse;
			//foreach ($reponse as $key=>$task) {
			//	$tasks[$key]['DT_RowID']=$task['ID'];
				//$tasks[$i]=$task;
				//$tasks[$i]['DT_RowID']=$task['ID'];
				//$tasks[$i][]=$task['ID'];

				//$debut_limite=$debut_limite+1;
				//$tasks[$i]['rownum']=$debut_limite;
				//$tasks[$i]['0']=$debut_limite;
			 // 	$tasks[$i]['id']=$rep['Gestparamphp_id'];
			 // 	$j=1;
			 // 	foreach ($colonnes as $colonne) {
			 // 		$tasks[$i][$j]=$rep[$colonne['Colonne']['name']];
			 // 		$j=$j+1;
			 // 	}
			  //	$i=$i+1;
			//}
		//}
		//debug($tasks);
		 //$tasks=$reponse;
		/*$tasks_filtre = $this->Task->find('count',
			array(
				'conditions' => $conditions
			)
		);*/
		
		/* Compte le nombre total d'enregistrement dans la table*/
		$requete = 'SELECT count(*) as COUNT ';
		if ($sonapp['Application']['hebergement']==0){
			$requete = $requete.' FROM '.$satable['Table']['name'].'_$'.$satable['Table']['id'];
		}else{
			if ($sonapp['Application']['pgdatabase_id']==1){
				$requete = $requete.' FROM '.$satable['Table']['name'];
			}elseif ($sonapp['Application']['pgdatabase_id']==2){
				$requete = $requete.' FROM '.$satable['Table']['name'];
			}
		}
		$reponse = $this->Maconnexion->dmlquery($sonapp, $requete);
		$nbTotalLignes = $reponse[0]['COUNT'];
			
		
		/* Compte le nombre d'enregistrement filtré par le where*/
		$requete = 'SELECT count(*) as COUNT ';
		if ($sonapp['Application']['hebergement']==0){
			$requete = $requete.' FROM '.$satable['Table']['name'].'_$'.$satable['Table']['id'];
		}else{
			if ($sonapp['Application']['pgdatabase_id']==1){
				$requete = $requete.' FROM '.$satable['Table']['name'];
			}elseif ($sonapp['Application']['pgdatabase_id']==2){
				$requete = $requete.' FROM '.$satable['Table']['name'];
			}
		}
		$requete = $requete.' WHERE '.$where_clause;
		$reponse = $this->Maconnexion->dmlquery($sonapp, $requete);
		$nbLignes_filtre=$reponse[0]['COUNT'];
		
		//Retravaille du tableau des taches pour ajouter les infos (supprimable ? par ex.)
		/*foreach ($tasks as $key => $task):
			if(	$task['Task']['etat_task_id'] == $task['TypeTask']['etat_cours_id']
				or 
				$task['Task']['etat_task_id'] == $task['TypeTask']['etat_realise_id']) 
			{
				$tasks[$key]['Infos']['deletable'] = 'O';
			}else{
				$tasks[$key]['Infos']['deletable'] = 'N';
			}
		endforeach;*/
		
		if (isset($this->params['url']['sEcho'])){
			$sEcho=	intval($this->params['url']['sEcho']);
		}else{
			$sEcho=	"1";
		}
		
		
		$output = array();
		$output['sEcho'] = $sEcho;//$this->params['url']['sEcho'];
		$output['iTotalRecords'] =  $nbTotalLignes;
		$output['iTotalDisplayRecords'] = $nbLignes_filtre;
		if($tasks!=null){
			$output['aaData'] = $tasks;
		}else{
			$output['aaData'] = array();
		}
		//echo json_encode($this->params);
		if($log>0){
			if($log>1){
				$this->Logging->lwrite(json_encode($output));
			}
			$this->Logging->lwrite('Fin ChargerLignes_ajax');
			$this->Logging->lwrite('------------------------------------------------------------------------------');
			$this->Logging->lclose();
		}
		echo json_encode($output);
		exit();
	}

	public function EditerLignes_ajax($table_id) {
		//debug($table_id);
		$log = 0;
		$this->layout = 'ajax';

		if($log>0){
			$this->Logging->lfile('D:\Program Files\wamp\www\GestParamPhp\log\Dynamique_ChargerLignes_ajax.log');
			$this->Logging->lwrite('------------------------------------------------------------------------------');
			$this->Logging->lwrite('Debut EditerLignes_ajax');

			if($log>0){
				foreach($this->params->data as $namepara => $valparam){
					$this->Logging->lwrite($namepara.' :'.$valparam);
				}
			}
		}

		$table_id = Sanitize::paranoid($table_id);
		$this->request->data = Sanitize::clean($this->params->data, array('encode' => false, 'unicode'=> true));
		
		/*$index_namecolonne_deb=strrpos($this->params->data['columnName'], 'title=') + 7;
		$index_namecolonne_fin=strrpos($this->params->data['columnName'], '" ' ,$index_namecolonne_deb) ;
		$columnName = substr( $this->params->data['columnName'], $index_namecolonne_deb , $index_namecolonne_fin-$index_namecolonne_deb);
		
		$ligneId = $this->params->data['id'];
		*/

		//$lacolonne = $this->Dynamique->Table->Colonne->find('first', array('limit'=> ($this->params->data['column'] - 1),'conditions' => array('Colonne.table_id =' => $table_id),'recursive'=>-1 ));
		$lacolonne = $this->Maconnexion->dmlquery(0, 'SELECT name from colonnes where table_id='.$table_id.' limit '.($this->params->data['column'] - 2).',1' );
		$columnName = $lacolonne[0]['name'];
		if($log>0){	$this->Logging->lwrite('$lacolonne : '.$columnName); }
		$ligneId = $this->params->data['row_id'];

		
		$value = str_replace('&nbsp;',' ',$this->params->data['value']);
		//debug($this->params->data);

		//debug($this->params->data['row_id']);

		$satable=$this->Dynamique->Table->find('first',array('conditions' => array('Table.id' => $table_id),'recursive'=>-1));
		$saversion=$this->Dynamique->Table->Version->find('first',array('conditions' => array('Version.id' => $satable['Table']['version_id']),'recursive'=>-1));
    	$sonapp = $this->Dynamique->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));

    	if($log>0){	$this->Logging->lwrite('$sonapp : '.$satable['Table']['id']); }

    	$requete = "SELECT count(".$columnName.") as count"
			." FROM ".$satable['Table']['name'].'_$'.$satable['Table']['id']
			." where Gestparamphp_id=".($ligneId + 1);

		if($log>1){	$this->Logging->lwrite('$requete : '.$requete); }
		$reponse = $this->Maconnexion->dmlquery($sonapp['Application']['id'], $requete );

		if($log>0){	$this->Logging->lwrite('count : '.$reponse[0]['count']); }

		if ($reponse[0]['count']=1){
			//$this->params->data['value'] = str_replace('&nbsp;',' ',$this->params->data['value']);
			$requete = "update ".$satable['Table']['name'].'_$'.$satable['Table']['id']
				." set ".$columnName."='".$value."'"
				." where Gestparamphp_id=".$ligneId;
			if($log>1){	$this->Logging->lwrite('$requete : '.$requete);	}
			$reponse = $this->Maconnexion->ddlquery($sonapp['Application']['id'], $requete );

			
		}

		$requete = "SELECT ".$columnName." as value"
			." FROM ".$satable['Table']['name'].'_$'.$satable['Table']['id']
			." where Gestparamphp_id=".$ligneId;
		$reponse = $this->Maconnexion->dmlquery($sonapp['Application']['id'], $requete );

		if($log>0){	$this->Logging->lwrite('$ligneId : '.$ligneId);	}
		if($log>0){	$this->Logging->lwrite('$reponse : '.$reponse[0]['value']);	}

		
		/*debug($this->params->data['column_name']);
		debug($this->params->data['value']);
		debug($this->params->data['row_id']);*/
		//echo 'moi';
		//echo str_replace(' ','&nbsp;',$reponse[0]['value']);
		if($log>0){
			$this->Logging->lwrite('Fin EditerLignes_ajax');
			$this->Logging->lwrite('------------------------------------------------------------------------------');
			$this->Logging->lclose();
		}
		echo $reponse[0]['value'] ;
		exit();
	}

	public function Editer($table_id) {

		$table_id = Sanitize::paranoid($table_id);
		$this->params['data'] = Sanitize::clean($this->params['data'], array('encode' => false, 'unicode'=> true));
		
		if ($this->request->is('post') || $this->request->is('put')) {
			$satable=$this->Dynamique->Table->find('first',array('conditions' => array('Table.id' => $table_id),'recursive'=>-1));
			$saversion=$this->Dynamique->Table->Version->find('first',array('conditions' => array('Version.id' => $satable['Table']['version_id']),'recursive'=>-1));
	    	$sonapp = $this->Dynamique->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));

			$colonnes = $this->Dynamique->Table->Colonne->find('all', array('conditions' => array('Colonne.table_id =' => $table_id),'recursive'=>-1 ));
			$sapk = $this->Dynamique->Table->Colonne->find('all', array('conditions' => array('Colonne.table_id =' => $table_id, 'Colonne.topPk =' => '1'),'recursive'=>-1 ));

			debug($this->request->data);

			$lstcolonnes="Gestparamphp_VERSION";
			foreach ($colonnes as $colonne) {
					$colonne=$colonne['Colonne'];
					$lstcolonnes=$lstcolonnes.", ".$colonne['name'];
			}

			//$this->Maconnexion->changer($sonapp['Application']['id']);
			foreach ($this->request->data['Ligne'] as $ligne) {
				$wherepk=" where Gestparamphp_VERSION=".$saversion['Version']['id'];
				foreach ($sapk as $pk) {
					$pk=$pk['Colonne'];
					$wherepk=$wherepk." and cast(".$pk['name']." AS CHAR)='".$ligne['Old'][$pk['name']]."'";
				}

				$lstvaleur=$saversion['Version']['id'];
				foreach ($colonnes as $colonne) {
					$colonne=$colonne['Colonne'];
					$lstvaleur=$lstvaleur.", '".$ligne[$colonne['name']]."'";
				}

				/*$reponse = $this->Maconnexion->dmlquery($sonapp['Application']['id'],
					"select count(*) as nb"
					." from ".$satable['Table']['name']."_$".$satable['Table']['id']
					.$where
					);*/

				//debug($where);
				//debug($reponse[0][0]['nb']);
				
				/*$reponse = $this->Maconnexion->dmlquery($sonapp['Application']['id'],
					"delete "
					." from ".$satable['Table']['name']."_$".$satable['Table']['id']
					.$wherepk
					);

				$reponse = $this->Maconnexion->dmlquery($sonapp['Application']['id'],
					"insert into ".$satable['Table']['name']."_$".$satable['Table']['id']
					."(".$lstcolonnes.")"
					." values(".$lstvaleur.") "
					);*/

			}
			//$this->Maconnexion->changer();
			$this->redirect(array('action' => 'DynamiqueOfTable',$table_id));
		} else {
			$satable=$this->Dynamique->Table->find('first',array('conditions' => array('Table.id' => $table_id),'recursive'=>-1));
			$saversion=$this->Dynamique->Table->Version->find('first',array('conditions' => array('Version.id' => $satable['Table']['version_id']),'recursive'=>-1));
	    	$sonapp = $this->Dynamique->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));

			$colonnes = $this->Dynamique->Table->Colonne->find('all', array('conditions' => array('Colonne.table_id =' => $table_id),'recursive'=>-1 ));
			$sapk = $this->Dynamique->Table->Colonne->find('all', array('conditions' => array('Colonne.table_id =' => $table_id, 'Colonne.topPk =' => '1'),'recursive'=>-1 ));

			//$this->Maconnexion->changer($sonapp['Application']['id']);
	        $reponse = $this->Maconnexion->dmlquery($sonapp['Application']['id'],'SELECT * FROM '.$satable['Table']['name'].'_$'.$satable['Table']['id'].' LIMIT 0,1');
	        //debug($reponse);

	        $i=0;
		    /*foreach ($reponse as $donnees)
		    { 
		    	$donnees=$donnees[$satable['Table']['name'].'_$'.$satable['Table']['id']];
		        $lignes[$i]['Ligne']['Gestparamphp_$']=$sonapp['Application']['id'];
		        $lignes[$i]['Ligne']['Gestparamphp_VERSION']=$donnees['Gestparamphp_VERSION'];
		        $lignes[$i]['Ligne']['Gestparamphp_VERSION_fin']=$donnees['Gestparamphp_VERSION_fin'];
				foreach ($colonnes as $colonne):
					$lignes[$i]['Ligne'][$colonne['Colonne']['name']] = $donnees[$colonne['Colonne']['name']];
				endforeach;
		        $i=$i+1;
		    } */
		    $lignes=$reponse;
		    //$this->Maconnexion->changer();

		    $tabpk="";
		    foreach ($sapk as $pk) {
		    	$pk=$pk['Colonne'];
		    	$tabpk[$pk['name']]="1";

		    }

			$this->set('sonapp', $sonapp);
			$this->set('saversion', $saversion);
			$this->set('satable', $satable);
			if (isset($colonnes)){
				$this->set('colonnes', $colonnes);
			}
			if (isset($tabpk)){
				$this->set('tabpk', $tabpk);
			}
			if (isset($lignes)){
				$this->set('lignes', $lignes);
			}
			$this->render("editer");
		}
	}

/**
 * view method
 *
 * @param string $id
 * @return void
 */
	public function view($id = null) {
		$this->Table->id = $id;
		if (!$this->Table->exists()) {
			throw new NotFoundException(__('Invalid table'));
		}
		$unetable = $this->Table->find('first',array('conditions' => array('Table.id' => $id),'recursive'=>-1));
		$saversion = $this->Table->Version->find('first',array('conditions' => array('Version.id' => $unetable['Table']['version_id']),'recursive'=>-1));
		$sonapp = $this->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));
		$this->set('saversion', $saversion);
		$this->set('sonapp', $sonapp);

		$data=$this->Table->read(null, $id);

		$i=0;
		foreach ($data['Colonne'] as $colonne): 
			$pgtypecol_name = $this->Table->Colonne->Pgtypecol->find('first',array('conditions' => array('id' => $colonne['pgtypecol_id']),'recursive'=>-1));
			$data['Colonne'][$i]['pgtypecol_name'] = $pgtypecol_name['Pgtypecol']['name'];
			$i=$i+1;
		endforeach;
		$this->set('table', $data);
	}

/**
 * add method
 *
 * @return void
 */
	public function add($idversion) {
		//debug($this->request->data);
		if ($this->request->is('post')) {
			//suppression de caractères spéciaux dans le nom de la table
			$this->request->data['Table']['name'] = Sanitize::paranoid($this->request->data['Table']['name'],array('_', '-')); 
			//Mise en majuscule du nom de la table
			$this->request->data['Table']['name'] = strtoupper($this->request->data['Table']['name']);

			$this->request->data = Sanitize::clean($this->request->data, array('encode' => false, 'unicode'=> true));
			debug($this->request->data);
			$this->Table->create();
			if ($this->Table->save($this->request->data)) {
				$this->Session->setFlash(__('The table has been saved'));
				//$this->redirect(array('action' => 'TablesOfVersion',$idversion));
			} else {
				$this->Session->setFlash(__('The table could not be saved. Please, try again.'));
			}
		}
		//$versions = $this->Table->Version->find('list');
		$this->set('idversion',$idversion);
		//$this->set(compact('versions'));
	}

/**
 * edit method
 *
 * @param string $id
 * @return void
 */
	public function edit($id = null) {
		$this->Table->id = $id;
		if (!$this->Table->exists()) {
			throw new NotFoundException(__('Invalid table'));
		}
		if ($this->request->is('post') || $this->request->is('put')) {
			//suppression de caractères spéciaux dans le nom de la table
			$this->request->data['Table']['name'] = Sanitize::paranoid($this->request->data['Table']['name'],array('_', '-')); 
			//Mise en majuscule du nom de la table
			$this->request->data['Table']['name'] = strtoupper($this->request->data['Table']['name']);
			
			$this->request->data = Sanitize::clean($this->request->data, array('encode' => false, 'unicode'=> true));
			
			if ($this->Table->save($this->request->data)) {
				$this->Session->setFlash(__('The table has been saved'));
				$this->redirect(array('action' => 'view',$id));
			} else {
				$this->Session->setFlash(__('The table could not be saved. Please, try again.'));
			}
		} else {
			$this->request->data = $this->Table->read(null, $id);
		}
		//$versions = $this->Table->Version->find('list');
		$this->set(compact('versions'));
	}

/**
 * delete method
 *
 * @param string $id
 * @return void
 */
	public function delete($id = null, $idversion) {
		if (!$this->request->is('post')) {
			throw new MethodNotAllowedException();
		}
		$this->Table->id = $id;
		//$laversion = $this->Table->find('first',array('conditions' => array('Table.id' => $id),'recursive'=>-1));
		if (!$this->Table->exists()) {
			throw new NotFoundException(__('Invalid table'));
		}
		if ($this->Table->delete()) {
			$this->Session->setFlash(__('Table deleted'));
			$this->redirect(array('action' => 'TablesOfVersion',$idversion));
		}
		$this->Session->setFlash(__('Table was not deleted'));
		$this->redirect(array('action' => 'index',$idversion));
	}
/****************************************************************************************************************/
}